﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using QLNhaSach;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public class ChiTietPhieuNhapDAL
    {
        DataService ds = new DataService();
        public ChiTietPhieuNhapDAL()
        {
        }
        public int InsertChiTietPhieuNhap(ChiTietPhieuNhap ctpn)
        {
            int kq = 0;
            try
            {
                String sql = "INSERT INTO CHITIETPHIEUNHAP (CTPN_MAPHIEUNHAP, CTPN_MASACH, CTPN_SOLUONGNHAP) VALUES (" + ctpn.MaPhieuNhap + ',' + ctpn.MaSach + ',' + ctpn.SoLuongNhap + ") SELECT @@Identity";
                kq = int.Parse(ds.ExcuteQuery(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int UpdateChiTietPhieuNhap(ChiTietPhieuNhap ctpn)
        {
            int kq = 0;
            try
            {
                String sql = "UPDATE CHITIETPHIEUNHAP SET CTPN_MASACH=" + ctpn.MaSach + ", CTPN_SOLUONGNHAP=" + ctpn.SoLuongNhap + " WHERE CTPN_MACHITIETPHIEUNHAP=" + ctpn.MaChiTietPhieuNhap;
                kq = ds.ExcuteInsert(sql);
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int DeleteChiTietPhieuNhap(int maChiTietPhieuNhap)
        {
            int kq = 0;
            try
            {
                String sql = "DELETE FROM CHITIETPHIEUNHAP WHERE CTPN_MACHITIETPHIEUNHAP=" + maChiTietPhieuNhap;
                kq = ds.ExcuteInsert(sql);
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
        public int GetTongSachNhapTrongThang(string thang, string nam, int maSach)
        {
            int kq = 0;
            try
            {
                String sql = "SELECT SUM(CTPN_SOLUONGNHAP) FROM PHIEUNHAP, CHITIETPHIEUNHAP, SACH WHERE";
                sql += " CTPN_MASACH = S_MASACH AND";
                sql += " CTPN_MAPHIEUNHAP = PN_MAPHIEUNHAP AND";
                sql += " CTPN_MASACH = " + maSach.ToString() + " AND";
                sql += " YEAR(PN_NGAYNHAP)='" + nam + "' AND MONTH(PN_NGAYNHAP) ='" + thang + "'";
                kq = int.Parse(ds.ExcuteQuery(sql).ToString());
            }
            catch (Exception)
            {
                throw;
            }
            return kq;

        }
    }
}
